package com.ttmusic.dao;

import java.util.ArrayList;
import java.util.List;

import com.ttmusic.entity.MusicList;
import com.ttmusic.entity.MusiclistMusicinfo;
import com.ttmusic.utils.SQLite.SQLiteUtil;

import android.database.Cursor;

public class MusicListDao implements BaseDao<MusicList>{
	
	//单例模式
	private MusicListDao() {}
	private static MusicListDao musicListDao = new MusicListDao();
	public static MusicListDao getInstance() {
		return musicListDao;
	}

	/**
	 * 查询所有歌单
	 */
	@Override
	public List<MusicList> findAll() {
		String sql = "select LIST_ID,LIST_NAME,NUM_SONGS,ICON_NAME,CREATE_TIME from TB_MUSIC_LIST ORDER BY LIST_ID ASC";
		Cursor cursor = SQLiteUtil.executeQuery(sql);
		List<MusicList> list = new ArrayList<MusicList>();
		if(cursor != null && cursor.getCount() > 0 && cursor.getColumnCount() > 0) {
			MusicList ml = null;
			while(cursor.moveToNext()) {
				ml = new MusicList();
				ml.setListId(cursor.getInt(cursor.getColumnIndex("LIST_ID")));
				ml.setListName(cursor.getString(cursor.getColumnIndex("LIST_NAME")));
				ml.setNumSongs(cursor.getInt(cursor.getColumnIndex("NUM_SONGS")));
				ml.setIconName(cursor.getString(cursor.getColumnIndex("ICON_NAME")));
				ml.setCreateTime(cursor.getLong(cursor.getColumnIndex("CREATE_TIME")));
				list.add(ml);
			}
			cursor.close();
		}
		return list;
	}

	/**
	 * 通过主键查询歌单
	 */
	@Override
	public MusicList loadById(Long id) {
		String sql = "select LIST_ID,LIST_NAME,NUM_SONGS,ICON_NAME,CREATE_TIME from TB_MUSIC_LIST WHERE LIST_ID=? ORDER BY LIST_ID ASC";
		Cursor cursor = SQLiteUtil.executeQuery(sql, id.toString());
		MusicList ml = null;
		if(cursor != null && cursor.getCount() > 0 && cursor.getColumnCount() > 0) {
			while(cursor.moveToNext()) {
				ml = new MusicList();
				ml.setListId(cursor.getInt(cursor.getColumnIndex("LIST_ID")));
				ml.setListName(cursor.getString(cursor.getColumnIndex("LIST_NAME")));
				ml.setNumSongs(cursor.getInt(cursor.getColumnIndex("NUM_SONGS")));
				ml.setIconName(cursor.getString(cursor.getColumnIndex("ICON_NAME")));
				ml.setCreateTime(cursor.getLong(cursor.getColumnIndex("CREATE_TIME")));
			}
			cursor.close();
		}
		return ml;
	}

	/**
	 * 通过歌单名查询歌单
	 */
	@Override
	public MusicList loadByName(String name) {
		String sql = "select LIST_ID,LIST_NAME,NUM_SONGS,ICON_NAME,CREATE_TIME from TB_MUSIC_LIST WHERE LIST_NAME=? ORDER BY LIST_ID ASC";
		Cursor cursor = SQLiteUtil.executeQuery(sql, name);
		MusicList ml = null;
		if(cursor != null && cursor.getCount() > 0 && cursor.getColumnCount() > 0) {
			while(cursor.moveToNext()) {
				ml = new MusicList();
				ml.setListId(cursor.getInt(cursor.getColumnIndex("LIST_ID")));
				ml.setListName(cursor.getString(cursor.getColumnIndex("LIST_NAME")));
				ml.setNumSongs(cursor.getInt(cursor.getColumnIndex("NUM_SONGS")));
				ml.setIconName(cursor.getString(cursor.getColumnIndex("ICON_NAME")));
				ml.setCreateTime(cursor.getLong(cursor.getColumnIndex("CREATE_TIME")));
			}
			cursor.close();
		}
		return ml;
	}

	/**
	 * 新增歌单
	 */
	@Override
	public long save(MusicList list) {
		//插入数据时不填主键  自动递增
		String sql = "insert into TB_MUSIC_LIST(LIST_NAME,NUM_SONGS,ICON_NAME,CREATE_TIME) values(?,?,?,?)";
		SQLiteUtil.executeDML(sql, list.getListName(), list.getNumSongs(), list.getIconName(), list.getCreateTime());
	
		//查找当前插入的数据主键值
		sql = "select MAX(LIST_ID) as MAX_LIST_ID from TB_MUSIC_LIST";
		Cursor c = SQLiteUtil.executeQuery(sql);
		if(c.getCount() > 0 && c.getColumnCount() > 0) {
			c.moveToNext();
			return c.getLong(c.getColumnIndex("MAX_LIST_ID"));
		}
		return -1;
	}

	/**
	 * 修改歌单
	 */
	@Override
	public void update(MusicList ml) {
		String sql = "update TB_MUSIC_LIST set LIST_NAME=?,NUM_SONGS=?,ICON_NAME=? where LIST_ID=?";
		SQLiteUtil.executeDML(sql, ml.getListName(), ml.getNumSongs(), ml.getIconName(), ml.getListId());
	}

	/**
	 * 删除歌单
	 * 		注意：删除歌单时还应该要删除歌单与歌曲的关联关系数据
	 */
	@Override
	public void delete(Long id) {
		String sql = "delete from TB_MUSIC_LIST where LIST_ID=?";
		SQLiteUtil.executeDML(sql, id);
	}
	
	
	//-----------------------------MusiclistMusicinfo关联数据--------------------------------
	/**
	 * 保存歌单与歌曲的关联关系数据
	 * @param mm
	 */
	public void saveMusiclistMusicinfo(MusiclistMusicinfo mm) {
		String sql = "insert into TB_MUSICLIST_MUSICINFO(LIST_ID,SONG_ID) values(?,?)";
		SQLiteUtil.executeDML(sql, mm.getListId(), mm.getSongId());
	}
	/**
	 * 通过歌单id和歌曲id查询 看是否已经有关联关系
	 * @param listId
	 * @param songId
	 * @return
	 */
	public MusiclistMusicinfo loadMusiclistMusicinfoByListIdAndSongId(Integer listId, Long songId) {
		String sql = "select LIST_INFO_ID,LIST_ID,SONG_ID from TB_MUSICLIST_MUSICINFO where LIST_ID=? and SONG_ID=?";
		Cursor c = SQLiteUtil.executeQuery(sql, listId.toString(), songId.toString());
		if(c.getCount() > 0 && c.moveToNext()) {
			MusiclistMusicinfo mm = new MusiclistMusicinfo();
			mm.setListInfoId(c.getInt(c.getColumnIndex("LIST_INFO_ID")));
			mm.setListId(c.getInt(c.getColumnIndex("LIST_ID")));
			mm.setSongId(c.getInt(c.getColumnIndex("SONG_ID")));
			return mm;
		}
		return null;
	}

}
